package com.allen.service;

import com.allen.Utils.JdbcUtils;
import com.allen.bean.Goods;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class GoodsService {
    private Connection connection;
    private Statement statement;
    private PreparedStatement preparedStatement;
    private ResultSet resultSet;

    public List<Goods> getAll() {
        List<Goods> goodses = new ArrayList<>();
        String sql = "SELECT * FROM goods;";

        connection = JdbcUtils.getConnect();
        try {
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                Goods goods = new Goods();
                goods.setGid(resultSet.getInt("gid"));
                goods.setGname(resultSet.getString("gname"));
                goods.setMerc(resultSet.getString("merc"));
                goods.setPlace(resultSet.getString("place"));
                goods.setInprice(resultSet.getDouble("inprice"));
                goods.setUnit(resultSet.getString("unit"));
                goodses.add(goods);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return goodses;
    }

    public Integer add(HttpServletRequest req, HttpServletResponse resp) {
        String gname = req.getParameter("gname");
        String merc = req.getParameter("merc");
        String place = req.getParameter("place");
        Double inprice = Double.valueOf(req.getParameter("inprice"));
        String unit = req.getParameter("unit");

        String sql = "insert into goods (gname,merc,place,inprice,unit) values(?,?,?,?,?)";
        connection = JdbcUtils.getConnect();
        Integer i = null;
        try {
            preparedStatement = connection.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
            preparedStatement.setString(1, gname);
            preparedStatement.setString(2, merc);
            preparedStatement.setString(3, place);
            preparedStatement.setDouble(4, inprice);
            preparedStatement.setString(5, unit);
            preparedStatement.executeUpdate();
            resultSet = preparedStatement.getGeneratedKeys();
            if (resultSet.next()){
                i = resultSet.getInt(1);
            }
            return i;

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return i;
    }

    public Goods findById(int gid) {
        Goods goods = new Goods();
        connection = JdbcUtils.getConnect();
        String sql = "select * from goods where gid=" + gid;
        try {
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {
                goods.setGid(resultSet.getInt("gid"));
                goods.setGname(resultSet.getString("gname"));
                goods.setMerc(resultSet.getString("merc"));
                goods.setPlace(resultSet.getString("place"));
                goods.setInprice(resultSet.getDouble("inprice"));
                goods.setUnit(resultSet.getString("unit"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                resultSet.close();
                statement.close();
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return goods;
    }

    public void update(HttpServletRequest req, HttpServletResponse resp) {
        Integer gid = Integer.valueOf(req.getParameter("gid"));
        String gname = req.getParameter("gname");
        String merc = req.getParameter("merc");
        String place = req.getParameter("place");
        Double inprice = Double.valueOf(req.getParameter("inprice"));
        String unit = req.getParameter("unit");
        String sql = "UPDATE goods SET gname=?,merc=?,place=?,inprice=?,unit=? WHERE gid=?";
        connection = JdbcUtils.getConnect();
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, gname);
            preparedStatement.setString(2, merc);
            preparedStatement.setString(3, place);
            preparedStatement.setDouble(4, inprice);
            preparedStatement.setString(5, unit);
            preparedStatement.setInt(6, gid);
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public void delete(Integer gid) {
        String sql = "DELETE FROM goods WHERE gid=?";
        connection = JdbcUtils.getConnect();
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, gid);
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}
